<?php

class ModelStockStockSearch extends Model {

	//获取库存数据总量
	public function getTotalInventories($data = array()) {

		$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "inventory i LEFT JOIN warehouse w ON i.warehouse_id = w.warehouse_id WHERE 1 ";
        //@author sonicsjh
        $sql = "SELECT COUNT(i.id) AS total FROM " . DB_PREFIX . "inventory i LEFT JOIN `warehouse` AS w ON (i.`warehouse_id` = w.`warehouse_id`) LEFT JOIN `product_option_value` AS pov ON (i.`product_code`=pov.`product_code`) LEFT JOIN  `vendor` AS v ON (pov.`product_id`=v.vproduct_id) WHERE 1 ";

		if (!empty($data['filter_product_code'])) {
			$sql .= " AND i.product_code = '".$data['filter_product_code']."' ";
		}

		if (!empty($data['filter_warehouse_type'])) {
			$sql .= " AND w.type = '".$data['filter_warehouse_type']."' ";
		}

		if (!empty($data['filter_warehouse'])) {
			$sql .= " AND i.warehouse_id = '".$data['filter_warehouse']."' ";
		}

		if (!empty($data['filter_warehouse_position'])) {
			$sql .= " AND i.position1 LIKE '%".$data['filter_warehouse_position']."%' OR i.position2 LIKE '%".$data['filter_warehouse_position']."%' ";
		}

        if (!empty($data['filter_vendor'])) {//@author sonicsjh
            $sql .= " AND v.vendor = '".$data['filter_vendor']."' ";
        }

		$query = $this->db->query($sql);
		return $query->row['total'];

	}
	
	//获取多个库存数据
	public function getInventories($data = array()){

		if($data){

			$sql = "
            SELECT
                p.sku,
                w.name as warehouse_name,
                pd.name as product_name,
                ovd.name as product_option,
                v.vendor,v.product_cost,
                i.* 
            FROM
                " . DB_PREFIX . "inventory i
                LEFT JOIN " . DB_PREFIX . "warehouse w ON i.warehouse_id = w.warehouse_id
                LEFT JOIN " . DB_PREFIX . "product_option_value pov ON i.product_code = pov.product_code
                LEFT JOIN " . DB_PREFIX . "product_description pd ON pov.product_id = pd.product_id
                LEFT JOIN " . DB_PREFIX . "product p ON p.product_id = pov.product_id
                LEFT JOIN " . DB_PREFIX . "vendor v ON pov.product_id = v.vproduct_id
                LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON pov.option_value_id = ovd.option_value_id
            WHERE 1 ";

			if (!empty($data['filter_warehouse'])) {
				$sql .= " AND i.warehouse_id = '".$data['filter_warehouse']."' ";
			}

			if (!empty($data['filter_product_code'])) {
				$sql .= " AND i.product_code = '".$data['filter_product_code']."' ";
			}

			if (!empty($data['filter_warehouse_position'])) {
				$sql .= " AND i.position1 LIKE '%".$data['filter_warehouse_position']."%' OR i.position2 LIKE '%".$data['filter_warehouse_position']."%' ";
			}

			if (!empty($data['filter_sku'])) {
				$sql .= " AND p.sku = '".$data['filter_sku']."' ";
			}

            if (!empty($data['filter_vendor'])) {//@author sonicsjh
                $sql .= " AND v.vendor = '".$data['filter_vendor']."' ";
            }

			if(isset($data['sort']) && in_array($data['sort'], $sort_data)){
				$sql .= " ORDER BY " . $data['sort']; 
			}
			else{
				$sql .= " ORDER BY i.id";  
			}
      
			if(isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			}
			else{
				$sql .= " ASC";
			}

			if(isset($data['start']) || isset($data['limit'])){

				if($data['start'] < 0){
					$data['start'] = 0;
				}

				if($data['limit'] < 1){
					$data['limit'] = 20;
				}

				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];

			}

			$query = $this->db->query($sql);
			return $query->rows;
		
		}
		else{

			$inventories_data = $this->cache->get('inventory');
			if(!$inventories_data){
				$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "inventory i");
				$inventories_data = $query->rows;
				$this->cache->set('warehouse', $inventories_data);
			}
			
			return $inventories_data;

		}

	}
	
	//获取单个商品
	public function getInventory($id){
		
		$sql = "SELECT w.name as warehouse_name, pd.name as product_name, ovd.name as option_name, p.sku as product_sku, i.* FROM " . DB_PREFIX . "inventory i ";
		$sql .= "LEFT JOIN " . DB_PREFIX . "warehouse w ON i.warehouse_id = w.warehouse_id ";
		$sql .= "LEFT JOIN " . DB_PREFIX . "product_option_value pov ON i.product_code = pov.product_code ";
		$sql .= "LEFT JOIN " . DB_PREFIX . "product p ON pov.product_id = p.product_id ";
		$sql .= "LEFT JOIN " . DB_PREFIX . "product_description pd ON pov.product_id = pd.product_id ";
		$sql .= "LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON pov.option_value_id = ovd.option_value_id WHERE i.id = ".$id;

		$query = $this->db->query($sql);
		return $query->row;

	}
	
	//获取所有仓库
	public function getWarehouses(){

		$gw = M("warehouse")
			->order("type ASC")
			->where("status = 1")
			->field("warehouse_id,name,parent_warehouse")->select();

		return $gw;

	}
	
	//获取商品
	public function getProducts($data = array()){
		
		$sql = "SELECT pd.name,pov.product_code FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN product_description pd ON pov.product_id = pd.product_id WHERE 1 ";

		if (!empty($data['filter_product'])) {
			$sql .= "AND pd.name LIKE '%".$data['filter_product']."%' OR pov.product_code LIKE '%".$data['filter_product']."%' ";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;

	}
	
	//编辑库存
	public function editInventory($id,$data){

		$data['id'] = (int)$id;
		$inventory = M("inventory");
		$inventory->data($data)->save();

	}
	
	//获取入库单相关单据
	public function getInHistory($id){

		$where['i.id'] = $id;
		$where['si.status'] = array('neq',0);

		//入库单
		$in = M('stock_in_detail')
			->alias('sid')
			->join(' stock_in si on si.in_id = sid.in_id','left')
			->join(' inventory i on sid.product_code = i.product_code and si.warehouse_id = i.warehouse_id','left')
			->join('user u on u.user_id = si.user_id','left')
			->join('refer_type rt on rt.refer_type_id = si.refer_type_id','left')
			->join('inout_state ios on ios.id = si.status','left')
			->field('"in" as type,si.in_id as id,rt.name as refer_name,si.refer_id as refer_id,"+" as calculate,sid.product_quantity as qty,sid.date_added as date_added,u.fullname as user_name,ios.id as stock_status,ios.name as status')
			->where($where)->order('date_added desc')->select();

		return $in;

	}

	//获取出库单相关单据
	public function getOutHistory($id){

		$where['i.id'] = $id;
		$where['so.status'] = array('neq',0);

		//出库单
		$out = M('stock_out_detail')
			->alias('sod')
			->join(' stock_out so on so.out_id = sod.out_id','left')
			->join(' inventory i on sod.product_code = i.product_code and so.warehouse_id = i.warehouse_id','left')
			->join('user u on u.user_id = so.user_id','left')
			->join('refer_type rt on rt.refer_type_id = so.refer_type_id','left')
			->join('inout_state ios on ios.id = so.status','left')
			->field('"out" as type,so.out_id as id,rt.name as refer_name,so.refer_id as refer_id,"-" as calculate,sod.product_quantity as qty,sod.date_added as date_added,u.fullname as user_name,ios.id as stock_status,ios.name as status')
			->where($where)->order('date_added desc')->select();

		return $out;

	}

	//获取损益单相关单据
	public function getTrimHistory($id){

		$where['i.id'] = $id;
		$where['t.status'] = 3;

		//损益单
		$trim = M('trim_product')
			->alias('tp')
			->join(' trim t on t.id = tp.trim_id','left')
			->join(' inventory i on i.product_code = tp.product_code and t.warehouse_id = i.warehouse_id','left')
			->join('user u on u.user_id = t.user_id','left')
			->field(' "trim" as type,t.id as id,"" as calculate,tp.qty as qty,t.date_added as date_added,u.fullname as user_name,t.status as trim_status,t.status as status ')
			->where($where)->order('date_added desc')->select();

		return $trim;

	}
	
	//获取指定id下所有相关单据
	public function getInventoryHistory($group){
		
		$gih = $group;

		/*按照时间排序*/
		foreach($gih as $key=>$v){
			$gih[$key]['date_added'] = date('Y-m-d H:i:s',strtotime($v['date_added']));
		} 
		$datetime = array();
		foreach ($gih as $v) {
			$datetime[] = $v['date_added'];
		}
		array_multisort($datetime,SORT_DESC,$gih);
		/*按照时间排序*/

		return $gih;

	}

    /*
     * 获取仓库中，需要补货的商品信息（安全库存-可用数量-入库在途 > 0）
     * @author sonicsjh
     */
    public function getProductsLTsafeQty($availableWarehouseList, $inventoryProducts) {
        if (is_array($availableWarehouseList) && count($availableWarehouseList) > 0) {
            $temp = array();
            foreach ($availableWarehouseList as $warehouseId => $canRecive) {
                $temp[] = "`warehouse_id`='".$warehouseId."'";
            }
            $sql = "SELECT `warehouse_id`,`product_code`,`available_quantity`,`safe_quantity`,`qty_in_onway` FROM `".DB_PREFIX."inventory` WHERE (".implode($temp, ' OR ').") AND `status`=1";
            $query = $this->db->query($sql);
            foreach ($query->rows as $row) {
                $num =  max(0, $row['safe_quantity']) - max(0, $row['available_quantity']) - max(0, $row['qty_in_onway']);
                if ($num > 0) {
                    $inventoryProducts[$row['warehouse_id']][$row['product_code']]['nums'] = $num;
                }
            }
        }
        return $inventoryProducts;
    }

    /*
     * 根据商品编码，获取库存汇总信息（可用数量，入库在途）
     * @author sonicsjh
     */
    public function getGatherQtyByProductCode($productCode) {
        $ret = array(
            'availableQty' => '',
            'qtyInOnway' => '',
            'safeQuantity' => '',
        );
        $sql = "SELECT I.`available_quantity`,I.`qty_in_onway`,I.`safe_quantity`,W.`name` FROM `".DB_PREFIX."inventory` AS I LEFT JOIN `warehouse` AS W ON (I.`warehouse_id`=W.`warehouse_id`) WHERE I.`product_code` LIKE '".$productCode."%' AND W.`type`<>'3' AND W.`status`='1' ORDER BY W.`type`,W.`warehouse_id`";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $ret['availableQty'] .= max(0, $row['available_quantity']).'（'.mb_substr($row['name'], 0, 2).'）<br />';
            $ret['qtyInOnway'] .= max(0, $row['qty_in_onway']).'（'.mb_substr($row['name'], 0, 2).'）<br />';
            $ret['safeQuantity'] .= max(0, $row['safe_quantity']).'（'.mb_substr($row['name'], 0, 2).'）<br />';
        }
        $ret['availableQty'] = substr($ret['availableQty'],0, -6);
        $ret['qtyInOnway'] = substr($ret['qtyInOnway'],0, -6);
        $ret['safeQuantity'] = substr($ret['safeQuantity'],0, -6);
        return $ret;
    }

    /*
     * 获取分仓库存汇总信息（可用数量，入库在途）
     * @author sonicsjh
     */
    public function getGatherQtyV2() {
        $ret = array();
        $sql = "SELECT `product_code` AS product_code,`available_quantity`,`warehouse_id` FROM `".DB_PREFIX."inventory` WHERE `warehouse_id` IN (SELECT `warehouse_id` FROM `".DB_PREFIX."warehouse` WHERE `type`<>'3' AND `status`='1')";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            $ret[$pCode][$row['warehouse_id']]['availableQty'] += $row['available_quantity'];
            //$ret[$pCode][$row['warehouse_id']]['qtyInOnway'] += $row['qty_in_onway'];
            //$ret[$pCode][$row['warehouse_id']]['safeQuantity'] += $row['safe_quantity'];
        }
        return $ret;
    }

    /*
     * 根据商品编码，获取分仓订购数量
     */
    public function getGatherBuyQtyByProductCode($productCode) {
        $ret = '';
        $sql = "SELECT W.`name`,SUM(PP.`qty`-PP.`delivered_qty`) AS buy_qty FROM `po_product` AS PP LEFT JOIN `po` AS PO ON (PP.`po_id`=PO.`id`) LEFT JOIN `warehouse` AS W ON (PO.`warehouse_id`=W.`warehouse_id`) WHERE PP.`product_code` LIKE '%".$productCode."%' AND (PO.`status`!='4' AND PO.`status`!='6' AND PO.`status`!='9') GROUP BY W.`name` DESC";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            if ('' == $row['name']) {
                continue;
            }
            $ret .= max(0, $row['buy_qty']).'（'.mb_substr($row['name'], 0, 2).'）<br />';
        }
        return substr($ret,0, -6);
    }

    /*
     * 获取所有商品的分仓订购数量
     */
    public function getGatherBuyQtyBy() {
        $sql = "SELECT po_product.product_code,SUM(po_product.price) AS price, SUM(po_product.qty - po_product.delivered_qty ) AS buyqty FROM po_product LEFT JOIN po ON po_product.po_id = po.id WHERE (po.`status` != 6 AND po.`status` != 9 AND po.`status` != 4 ) GROUP BY po_product.product_code";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            $ret[$pCode]['buyqty'] += max(0, $row['buyqty']);
            $ret[$pCode]['buyprice'] +=(float)$row['price'];
        }
        return $ret;
    }

    /*
     * 根据商品编码，获取分仓送货周期
     */
    public function getDeliveryCycle($product_code) {
        $ret = '';
        $startDay = date('Ymd', strtotime('-1 month'));
        $sql = "SELECT W.`name`,AVG(SID.`delivery_cycle`) AS AVGDC FROM `stock_in_detail` AS SID LEFT JOIN `stock_in` AS SI ON (SID.`in_id`=SI.`in_id`) LEFT JOIN `warehouse` AS W ON (SI.`warehouse_id`=W.`warehouse_id`) WHERE SID.`date_added`>'".$startDay."000000' AND SID.`product_code` LIKE '".$product_code."%' AND SI.`refer_type_id`='2' AND W.`type`<'3' GROUP BY SI.`warehouse_id` ORDER BY W.`name` ";
        //$sql = "SELECT AVG(stock_in_detail.delivery_cycle ) AS 'av' FROM stock_in_detail LEFT JOIN product_option_value ON stock_in_detail.product_code = product_option_value.product_code LEFT JOIN product ON product_option_value.product_id = product.product_id WHERE stock_in_detail.date_added >".$date.'000000 AND product.product_code ='.$product_code;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            if ('' == $row['name']) {
                continue;
            }
            $ret .= round($row['AVGDC']/86400, 2).'天（'.mb_substr($row['name'], 0, 2).'）<br />';
        }
        return substr($ret,0, -6);
    }

    /*
     * 获取所有商品的分仓送货周期
     */
    public function getDeliveryCycleall() {
        $date = date('Ymd', strtotime('-1 month'));
        $sql = "SELECT stock_in_detail.product_code, AVG(stock_in_detail.delivery_cycle ) AS 'av' FROM stock_in_detail WHERE stock_in_detail.date_added >".$date.'000000 group by stock_in_detail.product_code';
        $query = $this->db->query($sql);
        // echo $sql;
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            $av = $row['av']/86400;
            $ret[$pCode]['av'] = round($av,2);
        }
        return $ret;
    }
 
    /*
     * 根据商品编码，获取用户购买数量（按发货仓库划分）
     */
    public function getsaleqty($productCode,$flog) {
        $ret = '';
        $date = date('Ymd', strtotime('-2 month'));
        //获取单品销售的未发货数据
        $sql = "
        SELECT
            SUM(OP.`lack_quantity`) AS qty,OP.`product_code`,
            L.`default_warehouse` AS wh_id
        FROM
            `order_product` AS OP
            LEFT JOIN `order` AS O ON (O.`order_id`=OP.`order_id`)
            LEFT JOIN `logcenters` AS L ON (O.`logcenter_id`=L.`logcenter_id`)
        WHERE 
            (O.order_status_id = '1' OR O.order_status_id = '2' OR O.order_status_id = '3' OR O.order_status_id = '20' OR O.order_status_id = '17') AND O.verify_status != '2' AND O.date_added > '".$date."000000'
            AND OP.product_code ='".$productCode."0'
        GROUP BY
            L.`default_warehouse`
        ";
        // echo $sql;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $qtyInWarehouse[$row['wh_id']] += max(0, $row['qty']);
        }
        //获取商品组内部商品的未发货数据
        $sql = "
        SELECT
            SUM(OPG.`lack_quantity`) AS qty,OPG.`product_code`,
            L.`default_warehouse` AS wh_id
        FROM
            `order_product_group` AS OPG
            LEFT JOIN `order_product` AS OP ON (OPG.`order_product_id`=OP.`order_product_id`)
            LEFT JOIN `order` AS O ON (O.`order_id`=OP.`order_id`)
            LEFT JOIN `logcenters` AS L ON (O.`logcenter_id`=L.`logcenter_id`)
        WHERE 
            (O.order_status_id = '1' OR O.order_status_id = '2' OR O.order_status_id = '3' OR O.order_status_id = '20' OR O.order_status_id = '17') AND O.verify_status != '2' AND O.date_added > '".$date."000000'
            AND OPG.product_code ='".$productCode."0'
        GROUP BY
            L.`default_warehouse`
        ";
        // echo $sql;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $qtyInWarehouse[$row['wh_id']] += max(0, $row['qty']);
        }
        foreach ($qtyInWarehouse as $wh_id=>$qty) {
            if ($qty > 0 && $wh_id > 0) {
                if (4 == $wh_id) {
                    $tmp['无锡'] += $qty;
                }else{
                    $tmp['无锡'] += $qty;
                }
            }
        }
        krsort($tmp);
        foreach ($tmp as $wh=>$qty) {
            $ret .= $qty.'（'.$wh.'）<br />';
        }
        if ($flog==1) {
        	return $tmp;
        	
        }else{
        	return substr($ret,0, -6);
        }
        
    }


    public function getRo($product_id){
        $ret = '';
        $date = date('Ymd', strtotime('-4 week'));
        $sql = "SELECT r.id, r.date_added, r.`status`, r.warehouse_id, rp.id, rp.ro_id, rp.product_id, Sum(rp.qty) as qty, rp.unit_price, rp.product_code, w.`name` FROM ro AS r LEFT JOIN ro_product AS rp ON r.id = rp.ro_id LEFT JOIN warehouse AS w ON r.warehouse_id = w.warehouse_id WHERE r.`status` = 4  AND  r.date_added  > '".$date."000000' GROUP BY rp.product_code, r.warehouse_id";
        // echo $sql;die();
        $query = $this->db->query($sql);
        foreach ($query->rows as $key=>$value) {
            	$pCode = substr($value['product_code'], 0, 10);

                $tmp[$pCode].= $value['name'].':'.$value['qty'].'。';                
        }
      // var_dump($tmp);die();
        // foreach ($tmp as $wh=>$qty) {
        //     $ret .=$wh.':'.$qty.'。';
        // }

        return $tmp;
        
        
    }




/*
    public function getsaleqty($product_code) {
        $date = date('Ymd', strtotime('-2 month'));
        $sql = "SELECT
SUM(OG.lack_quantity) AS quantity,
OG.product_code
FROM
order_product AS OP
LEFT JOIN `order` AS O ON O.order_id = OP.order_id
LEFT JOIN product AS P ON P.product_id = OP.product_id
LEFT JOIN order_product_group AS OG ON OP.order_product_id = OG.order_product_id
WHERE
(O.order_status_id = '1' OR
O.order_status_id = '2' OR
O.order_status_id = '3' OR
O.order_status_id = '20' OR
O.order_status_id = '17') AND
O.verify_status != '2' AND
O.date_added > '20170902000000' AND  OG.product_code ='".$product_code."0'";
echo $sql.'<br />';
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            if (!empty($pCode)) {
                $ret[$pCode]['quantity'] = max(0, $row['quantity']);
                $ret[$pCode]['product_type'] = max(0, $row['product_type']);
            }
        }
        $sql = "SELECT
	SUM(OP.`lack_quantity`) AS 'quantity',
	P.`product_code`
FROM
	`order_product` AS OP
LEFT JOIN `order` AS O ON O.`order_id` = OP.`order_id`
LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id`
WHERE
	(
		O.order_status_id = '1'
		OR O.order_status_id = '2'
		OR O.order_status_id = '3' OR O.order_status_id = '20' OR O.order_status_id = '20' OR O.order_status_id = '17'
	) AND O.`verify_status` != '2' AND O.`date_added`  > '".$date."000000'AND P.product_code ='".$product_code."'";
        // echo $sql;

		$query = $this->db->query($sql);
		foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            if (!empty($pCode)) {
            	$ret[$pCode]['quantity'] += max(0, $row['quantity']);
            	$ret[$pCode]['product_type'] = max(0, $row['product_type']);
            	
            }
            // var_dump($row['quantity']);
    	}
    	// foreach ($ as $key => $value) {
    	// 	# code...
    	// }
        // var_dump($ret);
        // die();
        return $ret;
    } 
*/

    public function getAllsaleqty()
 	{
 		$date = date('Ymd', strtotime('-2 month'));
        $sql = "SELECT
Sum(OG.lack_quantity) AS quantity,
OG.product_code
FROM
order_product AS OP
LEFT JOIN `order` AS O ON O.order_id = OP.order_id
LEFT JOIN product AS P ON P.product_id = OP.product_id
LEFT JOIN order_product_group AS OG ON OP.order_product_id = OG.order_product_id
WHERE
(O.order_status_id = '1' OR
O.order_status_id = '2' OR
O.order_status_id = '3' OR
O.order_status_id = '20' OR
O.order_status_id = '17') AND
O.verify_status != '2' AND
O.date_added > '20170902000000' group BY  OG.product_code";
// echo $sql;
$query = $this->db->query($sql);
		foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            if (!empty($pCode)) {
            	$ret[$pCode]['quantity'] = max(0, $row['quantity']);
            	$ret[$pCode]['product_type'] = max(0, $row['product_type']);
            	
            }
            // var_dump($row['quantity']);
    	}
    		// var_dump($ret);
     	$sql = "SELECT
	SUM(OP.`lack_quantity`) AS 'quantity',
	P.`product_code`
FROM
	`order_product` AS OP
LEFT JOIN `order` AS O ON O.`order_id` = OP.`order_id`
LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id`
WHERE
	(
		O.order_status_id = '1'
		OR O.order_status_id = '2'
		OR O.order_status_id = '3' OR O.order_status_id = '20' OR O.order_status_id = '20' OR O.order_status_id = '17'
	) AND O.`verify_status` != '2' AND O.`date_added`  > '".$date."000000' group by  P.product_code";
        // echo $sql;

		$query = $this->db->query($sql);
		foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            if (!empty($pCode)) {
            	$ret[$pCode]['quantity'] += max(0, $row['quantity']);
            	$ret[$pCode]['product_type'] = max(0, $row['product_type']);
            	
            }
            // var_dump($row['quantity']);
    	}
        return $ret;
    } 



    /*
     * 获取所有库存汇总信息，基于商品主信息（可用数量，入库在途）
     * @author sonicsjh
     */
    public function getGatherQty() {
        $ret = array();
        $sql = "SELECT I.`product_code`,I.`available_quantity`,I.`qty_in_onway`,I.`safe_quantity`,W.`name` FROM `".DB_PREFIX."inventory` AS I LEFT JOIN `warehouse` AS W ON (I.`warehouse_id`=W.`warehouse_id`) WHERE W.`type`<>'3' AND W.`status`='1'";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            $ret[$pCode]['availableQty'] += max(0, $row['available_quantity']);
            $ret[$pCode]['qtyInOnway'] += max(0, $row['qty_in_onway']);
            $ret[$pCode]['qtySafe'] += max(0, $row['safe_quantity']);
            /*
            $ret[$pCode]['汇总']['availableQty'] += max(0, $row['available_quantity']);
            $ret[$pCode]['汇总']['qtyInOnway'] += max(0, $row['qty_in_onway']);
            $ret[$pCode][$row['name']]['availableQty'] += max(0, $row['available_quantity']);
            $ret[$pCode][$row['name']]['qtyInOnway'] += max(0, $row['qty_in_onway']);
            */
        }
        return $ret;
    }


              /*
     * 根据商品编码，获取平均进货价getsaleqty($result['product_id'])
     *
     */
	public function getAgvBuy()
 	{
 		$date = date('Ymd', strtotime('-1 month'));
        $sql = "SELECT po_product.product_code, SUM(po_product.unit_price* po_product.delivered_qty)/SUM(po_product.delivered_qty) as 'buyav' FROM po_product GROUP BY po_product.product_code";
		$query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            if (!empty($pCode)) {
            	$ret[$pCode]['buyav']= max(0, $row['buyav']);
            	
            }
    
        }
        return $ret;
    } 


                  /*
     * 计算补货量
     *
     */
	public function deliverycycle()
 	{
        $sql = "SELECT AVG(si.delivery_cycle) AS delivery_cycle , sid.product_code FROM stock_in_detail AS sid LEFT JOIN stock_in AS si ON sid.in_id = si.in_id WHERE si.refer_type_id =2 AND  si.delivery_cycle!=0 GROUP BY sid.product_code ";
		$query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            $deliverycycle =  $row['delivery_cycle']/86400;
            if (!empty($pCode)) {
            	$ret[$pCode]['deliverycycle']= $deliverycycle ;
            	
            }
    
        }
        return $ret;
    } 
    //获取日销量
    public function getdaysale()
 	{
        $date = date('Ymd', strtotime('-2 week'));

        $sql = "SELECT order_product.product_code, sum(order_product.quantity) as quantity,order_product.product_type,order_product.order_product_id  FROM `order_product` WHERE order_id IN (SELECT o.order_id FROM `order` AS o WHERE o.order_status_id <> 16 AND o.order_status_id <> 0 AND o.order_status_id <> 13 AND o.order_status_id <> 11 AND o.date_added>'".$date."000000') group by order_product.product_code";
		$query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            $ret[$pCode]['quantity']+=max(0, $row['quantity']);


        	if ($row['product_type']==2) {
        		$sql = "SELECT order_product_group.order_product_id, order_product_group.product_code, order_product_group.quantity FROM order_product_group where order_product_group.order_product_id =".$row['order_product_id'];
				$query = $this->db->query($sql);
				foreach ($query->rows as $key => $value) {
					$pCode = substr($row['product_code'], 0, 10);
            		$ret[$pCode]['quantity']+=max(0, $row['quantity']);
				}
        	}
    
        }
        return $ret;
    } 

    public function getInventorynumber($daysale,$poqty) {
        $ret = array();
        $sql = "SELECT `product_code` AS product_code,SUM(`available_quantity`) as available_quantity,sum(safe_quantity) as safe_quantity  FROM `".DB_PREFIX."inventory` WHERE `warehouse_id` IN (SELECT `warehouse_id` FROM `".DB_PREFIX."warehouse` WHERE `type`<>'3' AND `status`='1')
        	group by product_code
        ";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            $ret[$pCode]['available_quantity'] = $row['available_quantity'];
            $ret[$pCode]['safe_quantity'] = $row['safe_quantity'];
            $ret[$pCode]['poqty'] = $poqty[$pCode]['poqty'];
            $ret[$pCode]['quantity'] = $daysale[$pCode]['quantity'];
            //$ret[$pCode][$row['warehouse_id']]['qtyInOnway'] += $row['qty_in_onway'];
            //$ret[$pCode][$row['warehouse_id']]['safeQuantity'] += $row['safe_quantity'];
             $mums = 7*((float)$daysale[$pCode]['quantity']/14)-(float)$row['available_quantity']+$ret[$pCode]['poqty'];
             $ret[$pCode]['nums'] = (int)$mums;
        }
        return  $ret;
    }

    /*
     * 计算采购金额
     *
     */
	public function getpoqty()
 	{
        $sql = "SELECT p.po_id, p.product_code, (p.qty- p.delivered_qty) AS poqty FROM po_product AS p WHERE p.po_id IN (SELECT po.id FROM `po` WHERE po.`status` = 0 OR po.`status` = 1 OR po.`status` = 2 OR po.`status` = 3 OR po.`status` = 10 )";
		$query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['product_code'], 0, 10);
            if (!empty($pCode)) {
            	$ret[$pCode]['poqty']+= $row['poqty'];
            	
            }
    
        }
        return $ret;
    } 

}
?>
